using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
using System.Collections;
using Microsoft.AceEngineering.SPM.SPMCustomDataEntity;
using System.Web.UI.WebControls;

namespace Microsoft.AceEngineering.SPM.SPMDataLayer
{
    public class SearchDL
    {
        public static ArrayList GetAttributes()
        {
            SqlDataReader sqlreader = null;
            ArrayList attributes = new ArrayList();
            ListItem lstItem = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spGetAttributes");
                while (sqlreader.Read())
                {
                    lstItem = new ListItem();
                    lstItem.Text = sqlreader.GetString(0);
                    lstItem.Value = sqlreader.GetValue(1).ToString();
                    attributes.Add(lstItem);
                    lstItem = null;
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return attributes;
        }

        public static List<SearchDataListCDE> GetAppSearchList(string attributes, string appName, int thresholdScoreID, int orgID, int startIndex, int noOfrows, string sortExp)
        {
            SqlDataReader sqlreader = null;
            List<SearchDataListCDE> appList = new List<SearchDataListCDE>();
            SearchDataListCDE appRow = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[7];
                myParams[0] = new SqlParameter("@AttributeFilter", SqlDbType.VarChar);
                if (string.IsNullOrEmpty(attributes))
                    myParams[0].Value = null;
                else
                    myParams[0].Value = attributes;

                myParams[1] = new SqlParameter("@AppName", SqlDbType.VarChar);
                myParams[1].Value = appName;

                myParams[2] = new SqlParameter("@ScoreThresholdID", SqlDbType.Int);
                if (thresholdScoreID != -1)
                    myParams[2].Value = thresholdScoreID;
                else
                    myParams[2].Value = null;

                myParams[3] = new SqlParameter("@OrgID", SqlDbType.Int);
                if (orgID != -1)
                    myParams[3].Value = orgID;
                else
                    myParams[3].Value = null;

                myParams[4] = new SqlParameter("@sortExp", SqlDbType.VarChar);
                myParams[4].Value = sortExp;

                myParams[5] = new SqlParameter("@startRowIndex", SqlDbType.Int);
                myParams[5].Value = startIndex;

                myParams[6] = new SqlParameter("@maximumRows", SqlDbType.Int);
                myParams[6].Value = noOfrows;
                
                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spSearch", myParams);
                while (sqlreader.Read())
                {
                    appRow = new SearchDataListCDE();
                    appRow.AppName = (string)sqlreader.GetValue(0);
                    appRow.StartDate = (string)sqlreader.GetValue(1);
                    appRow.Score = (string)sqlreader.GetValue(2).ToString();
                    appRow.AppVersion = (string)sqlreader.GetValue(3);
                    appRow.AppID = (int)sqlreader.GetValue(4);
                    appRow.AppRisk = (string)sqlreader.GetValue(5).ToString();
                    appRow.OrgName = (string)sqlreader.GetValue(6).ToString();
                    appList.Add(appRow);
                    appRow = null;
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return appList;
        }

        public static int GetAppSearchListCount(string attributes, string appName, int thresholdID, int orgID)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[4];
            myParams[0] = new SqlParameter("@AttributeFilter", SqlDbType.VarChar);
            if (string.IsNullOrEmpty(attributes))
                myParams[0].Value = null;
            else
                myParams[0].Value = attributes;

            myParams[1] = new SqlParameter("@AppName", SqlDbType.VarChar);
            myParams[1].Value = appName;

            myParams[2] = new SqlParameter("@ScoreThresholdID", SqlDbType.Int);
            if (thresholdID != -1)
                myParams[2].Value = thresholdID;
            else
                myParams[2].Value = null;

            myParams[3] = new SqlParameter("@OrgID", SqlDbType.Int);
            if (orgID != -1)
                myParams[3].Value = orgID;
            else
                myParams[3].Value = null;

            return (int)SqlHelper.ExecuteScalar(conString, CommandType.StoredProcedure, "SPM_spSearchCount", myParams);
        }

        public static List<AssesmentCDE> GetAssesment(int AppID)
        {
            SqlDataReader sqlreader = null;
            List<AssesmentCDE> appList = new List<AssesmentCDE>();
            AssesmentCDE appRow = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@AppID", SqlDbType.Int);
                myParams[0].Value = AppID;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spGetAssesmentReport", myParams);
                while (sqlreader.Read())
                {
                    appRow = new AssesmentCDE();
                    appRow.SortOrder = (int)sqlreader.GetValue(0);
                    if (sqlreader.GetValue(3).GetType().ToString() == "System.DBNull")
                        appRow.Answer = string.Empty;
                    else
                        appRow.Answer = (string)sqlreader.GetValue(3);
                    appRow.Category = (string)sqlreader.GetValue(1);
                    appRow.Question = (string)sqlreader.GetValue(2);
                    appList.Add(appRow);
                    appRow = null;
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return appList;
        }

        public static ArrayList GetScoreThresholds()
        {
            SqlDataReader sqlreader = null;
            ArrayList scoreNames = new ArrayList();
            ListItem lstItem = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spGetScoreThreshold");
                while (sqlreader.Read())
                {
                    lstItem = new ListItem();
                    lstItem.Text = sqlreader.GetString(1);
                    lstItem.Value = sqlreader.GetValue(0).ToString();
                    scoreNames.Add(lstItem);
                    lstItem = null;
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }

            // To support no selection.
            lstItem = new ListItem();
            lstItem.Text = "No Selection";
            lstItem.Value = "-1";
            scoreNames.Insert(0, lstItem);
            return scoreNames;
        }

        public static ArrayList GetOrganizations()
        {
            SqlDataReader sqlreader = null;
            ArrayList organizationnames = new ArrayList();
            ListItem lstItem = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spGetFlatOrgListAll");
                while (sqlreader.Read())
                {
                    lstItem = new ListItem();
                    lstItem.Text = sqlreader.GetString(1);
                    lstItem.Value = sqlreader.GetValue(0).ToString();
                    organizationnames.Add(lstItem);
                    lstItem = null;
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }

            // To support no selection.
            lstItem = new ListItem();
            lstItem.Text = "No Selection";
            lstItem.Value = "-1";
            organizationnames.Insert(0, lstItem);
            return organizationnames;
        }
    }
}
